import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/user.dart';
import '../models/file_item.dart';

class DatabaseService {
  static Database? _database;
  static const String _databaseName = 'xiaosen_cloud_pan.db';
  static const int _databaseVersion = 1;

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), _databaseName);
    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
    );
  }

  Future<void> initDatabase() async {
    await database;
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        created_at TEXT NOT NULL,
        updated_at TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE files (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        path TEXT NOT NULL,
        minio_object_name TEXT NOT NULL,
        size INTEGER NOT NULL,
        mime_type TEXT NOT NULL,
        user_id INTEGER NOT NULL,
        uploaded_at TEXT NOT NULL,
        updated_at TEXT,
        is_folder INTEGER DEFAULT 0,
        parent_path TEXT,
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
      )
    ''');

    await db.execute('''
      CREATE INDEX idx_files_user_id ON files (user_id)
    ''');

    await db.execute('''
      CREATE INDEX idx_files_parent_path ON files (parent_path)
    ''');
  }

  // User operations
  Future<int> insertUser(User user) async {
    final db = await database;
    return await db.insert('users', user.toMap());
  }

  Future<User?> getUserByEmail(String email) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'users',
      where: 'email = ?',
      whereArgs: [email],
    );

    if (maps.isNotEmpty) {
      return User.fromMap(maps.first);
    }
    return null;
  }

  Future<User?> getUserByUsername(String username) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'users',
      where: 'username = ?',
      whereArgs: [username],
    );

    if (maps.isNotEmpty) {
      return User.fromMap(maps.first);
    }
    return null;
  }

  Future<User?> getUserById(int id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'users',
      where: 'id = ?',
      whereArgs: [id],
    );

    if (maps.isNotEmpty) {
      return User.fromMap(maps.first);
    }
    return null;
  }

  // File operations
  Future<int> insertFile(FileItem file) async {
    final db = await database;
    return await db.insert('files', file.toMap());
  }

  Future<List<FileItem>> getFilesByUserId(int userId, {String? parentPath}) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'files',
      where: parentPath != null ? 'user_id = ? AND parent_path = ?' : 'user_id = ? AND parent_path IS NULL',
      whereArgs: parentPath != null ? [userId, parentPath] : [userId],
      orderBy: 'is_folder DESC, name ASC',
    );

    return List.generate(maps.length, (i) => FileItem.fromMap(maps[i]));
  }

  Future<FileItem?> getFileById(int id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'files',
      where: 'id = ?',
      whereArgs: [id],
    );

    if (maps.isNotEmpty) {
      return FileItem.fromMap(maps.first);
    }
    return null;
  }

  Future<int> updateFile(FileItem file) async {
    final db = await database;
    return await db.update(
      'files',
      file.toMap(),
      where: 'id = ?',
      whereArgs: [file.id],
    );
  }

  Future<int> deleteFile(int id) async {
    final db = await database;
    return await db.delete(
      'files',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<List<FileItem>> searchFiles(int userId, String query) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'files',
      where: 'user_id = ? AND name LIKE ?',
      whereArgs: [userId, '%$query%'],
      orderBy: 'name ASC',
    );

    return List.generate(maps.length, (i) => FileItem.fromMap(maps[i]));
  }

  Future<int> createFolder(String name, String path, int userId, {String? parentPath}) async {
    final folder = FileItem(
      name: name,
      path: path,
      minioObjectName: '',
      size: 0,
      mimeType: 'folder',
      userId: userId,
      uploadedAt: DateTime.now(),
      isFolder: true,
      parentPath: parentPath,
    );
    return await insertFile(folder);
  }

  Future<void> close() async {
    final db = await database;
    await db.close();
  }
}